declare
cursor c_cont is                  select       distinct *    from asm_DW_CONTRATTO_ST1   -- where cuf_id =7813
                                                                                                       order by to_number(cuf_id);
                                                                                                                                                                            
   
   
   
   
    
     type parid_array is table  OF  varchar2(500); 
   
      parotid  parid_array;
      pardes parid_array;  
      visin parid_array;
      valin parid_array;
      type par_array is table of  varchar2(500);
      parin par_array;
      type parou_array is  VARRAY(50) of varchar2(500);
      parou  parou_array;
      valou   parou_array;
      visou   parou_array;
      type mqti_array is  VARRAY(10) of varchar2(500);
      ccid mqti_array;
      mqt mqti_array;
      
   tcondes     varchar2 (100);
 desatt varchar2 (500);
  v_errore                 NUMBER(1);
  v_errore1                 NUMBER(1);
  v_str_err                VARCHAR2(2000);
  mquadri number;
 titolare varchar2(100);
 j integer;
 tcon_des varchar2(200);

BEGIN
execute immediate 'truncate table asm_DW_CONTRATTO_ST3';
execute immediate 'truncate table asm_dw_contratti_error';
      ccid:= mqti_array();
      ccid.EXTEND(10); 
      mqt:= mqti_array();
     mqt.EXTEND(10); 
parou:= parou_array();
parou.EXTEND(50); 
visou:= parou_array();
visou.EXTEND(50); 
valou:= parou_array();
valou.EXTEND(50); 

parin := par_array ('TIPO_UTI','NUM_COM_>_CALC','NUM_COM_<_CALC','NUM_COM_>','NUM_COM_<','RIDUZIONE','TARSU','REDDITO_MIN','MQ_DIF_RID',
                                         'PERC_RID_PROM','PERC_RSAU','NUM_COM_FC','T1_FUORIZONA_PARVAR','T1_FZ_PV_PIEDILUCO','CASE_A_DISPOSIZIONE','UTENZE_STAGIONALI',
                                         'T8_HANDICAP_PF','IST_SCOLASTICI','TIPO_ATTIVITA');
                                         
                             --        insert into asm_DW_CONTRATTO_ST3  values (0,
                             --                                to_char(sysdate,'dd/mm/yyyy hh:mi:ss'),'','','','','','','','','','','','','','','','','',
                              --                                                       '','','','','','','','','','','','','','','','','','' ); 
          
         
            commit;
   for r_cont IN c_cont LOOP
    
    if r_cont.tcon_id='10' then tcon_des:='DOMESTICO'; else tcon_des:='NON DOMESTICO'; End IF;
    
     begin
                                                     

         select parot_id,parot_des,PAROT_VALVIS, VPXCO_VAL
                bulk collect into parotid, pardes,visin,valin
                from asm_DW_CONTRATTO_ST2 T2
                 where T2.ver_num =  r_cont.ver_num;
                 
                  --459194 
               
        if SQL%ROWCOUNT = 0 then
              

      
           v_str_err := 'vernum cuf non in parot :'  || rtrim(  r_cont.cuf_id) || rtrim(  r_cont.ver_num) ||' ';
           v_errore := 1;
           GOTO fine_loop;
       end if;   
         
           
        end;
       
        desatt:='';
                                           
                                                                                          commit; 
         for i in 1..parotid.LAST loop  
               j:=1;
               <<SU>> 
                 
                            if parotid(i) = parin(j) then  parou(j):=parotid(i);
                                                                                                valou(j):=valin(i);
                                                                         if parotid(i)='TIPO_ATTIVITA' then desatt:=visin(i);
                                                                            
                                                                                
                            end if;
                            j:=parin.last;
                          end if;
               if j<parin.last then j:=j+1; goto su; end if;
           
         end loop;
      mqt(1):='';
      mqt(2):='';
       mquadri:='';
      titolare:='';
      
         begin
        ----------------------------------------MQUADRI,titolare
                  select v.CCON_ID,v.VCCUF_VAL  
                  bulk collect into ccid,mqt
            from  valCXCUF  v
                
            where    
                  v.az_id= '01'
                  and v.cuf_id = r_cont.cuf_id
                  and v.CCON_ID in(1,22)
                     and     (SELECT CASE
                     WHEN ista.iss_staid <> 0
                     AND ista.iss_staidsuper = 0
                     AND ista.iss_sstaidsuper = 0
                        THEN ista.iss_staid
                     WHEN ista.iss_staid <> 0
                     AND ista.iss_staidsuper <> 0
                     AND ista.iss_sstaidsuper = 0
                        THEN ista.iss_staidsuper
                     WHEN ista.iss_staid <> 0
                     AND ista.iss_staidsuper <> 0
                     AND ista.iss_sstaidsuper <> 0
                        THEN ista.iss_sstaidsuper
                     ELSE -1
                  END
             FROM ista ista
            WHERE ista.az_id = v.az_id
              AND ista.csi_id = v.csi_id
              AND ista.ista_verstato = v.ista_verstato) = 2
                 AND V.VER_DTFINE = TO_DATE('31/12/9999','DD/MM/YYYY' );
             if SQL%ROWCOUNT = 0 then
           -- Non esiste una utenza con quel codice contratto
           v_str_err := ': '  || ' cuf_id su valCXCUF : ' || rtrim(  r_cont.cuf_id) ||' ->  INESISTENTE <-';
           v_errore := 1;
           GOTO ok1;
           end if;
                  
        
        end;

       
     if ccid(1)=1 then  mquadri:=mqt(1);
                                       titolare:=mqt(2);
                                       else 
                                      mquadri:=mqt(2);
                                      titolare:=mqt(1);
      end if;
        <<ok1>>

      
                                         insert into asm_DW_CONTRATTO_ST3  values (
                                         r_cont.sog_id,
                                        r_cont.sog_ragsoc,
                                        r_cont.sog_codfis ,
                                         r_cont.sog_pariva ,
                                        r_cont.con_anno,
                                        r_cont.con_id,
                                        r_cont.cuf_id,
                                        r_cont.TCON_ID,
                                              tcon_des,
                                            r_cont.ver_dtini,
                                        r_cont.ver_dtfine,
                                        r_cont.SITO_ID,
                                        r_cont.ELETOPO_DES,
                                        r_cont.IND_NUM,
                                        r_cont.COM_DES,
                                       
                                      
                                 
                                        mquadri,
                                        titolare,
                                        r_cont.gruppo_fat,

                                                  
                                                                                                                                    valou(1),
                                                                                                                                    valou(2),
                                                                                                                                    valou(3),
                                                                                                                                    valou(4),
                                                                                                                                    valou(5),
                                                                                                                                    valou(6),
                                                                                                                                    valou(7),
                                                                                                                                    valou(8),
                                                                                                                                    valou(9),
                                                                                                                                    valou(10),
                                                                                                                                    valou(11),
                                                                                                                                    valou(12),
                                                                                                                                    valou(13),
                                                                                                                                    valou(14),
                                                                                                                                    valou(15),
                                                                                                                                    valou(16),
                                                                                                                                    valou(17),
                                                                                                                                    valou(18),
                                                                                                                                    valou(19),
                                                                                                                               desatt
                                         
                                                    );

                                                                                                                                
                                                  
                                                  
                                                  
                                                  
                                                    
         <<fine_loop>> 
        commit;
              ccid:= mqti_array();
      ccid.EXTEND(10); 
      mqt:= mqti_array();
     mqt.EXTEND(10); 
          parou:= parou_array();
        parou.EXTEND(50); 
        visou:= parou_array();
        visou.EXTEND(50); 
        valou:= parou_array();
        valou.EXTEND(50); 
         
         
    COMMIT;
     if v_errore = 1
     then 
         insert into asm_dw_contratti_error  values( sysdate,'TEST '||v_str_err);
       COMMIT;
       v_errore:=0;
      end if;  
   end loop; 
                             --        insert into asm_DW_CONTRATTO_ST3  values (99999999999,
                             --                                to_char(sysdate,'dd/mm/yyyy hh:mi:ss'),'','','','','','','','','','','','','','','','','',
                              --                                                       '','','','','','','','','','','','','','','','','','' ); 
            commit;  
   
   
     
end;